"""
@Author : 合肥一元数智教育科技有限公司
@Date :  2025/3/21 14:48
@Description :  数据库操作工具类

面向对象思想
1. 初始化一个连接池
2. 获取连接  游标
3. 释放资源
4. 增删改的 function
5. fetchone()
7. fetchall()
8. fetchmany()

"""
import pymysql
from dbutils.pooled_db import PooledDB


class DBHelper:
    def __init__(self):
        self.pool = PooledDB(
            host='localhost',
            port=3306,
            user='root',
            password='root',
            db='python2511',
            charset='utf8',
            creator=pymysql,
            maxconnections=5,
            mincached=2,
            maxcached=3,
            blocking=True
        )

    # 获取连接  游标
    def get_connection_cursor(self):
        connection = self.pool.connection()
        cursor = connection.cursor()
        return connection, cursor

    #  释放资源
    def close_all(self, *args):
        for item in args:
            item.close()

    #  增删改   **kwargs
    def execute(self, sql, **kwargs):
        conn, cur = self.get_connection_cursor()
        line = cur.execute(sql, kwargs)
        # 提交事务
        conn.commit()
        self.close_all(conn, cur)
        return line

    # 查询一个
    def fetchone(self, sql, **kwargs):
        conn, cur = self.get_connection_cursor()
        line = cur.execute(sql, kwargs)
        result = cur.fetchone()
        self.close_all(conn, cur)
        return  result

    # 查询所有
    def fetchall(self, sql, **kwargs):
        conn, cur = self.get_connection_cursor()
        line = cur.execute(sql, kwargs)
        result = cur.fetchall()
        self.close_all(conn, cur)
        return result

    # 查询制定条数
    def fetchmany(self, sql, *args, **kwargs):
        conn, cur = self.get_connection_cursor()
        line = cur.execute(sql, kwargs)
        result = cur.fetchmany(args[0])
        self.close_all(conn, cur)
        return result


# 单例模式
db_helper = DBHelper()
